<?php

/*
 * Copyright (C) 2006 - 2010 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * The object used for executing a static SQL statement and returning the results
 * it produces
 *
 * By default, only one <code>SpicaOracleResultSet</code> object
 * per <code>SpicaOracleStatement</code> object can be open at the same time
 *
 * Therefore, if the reading of one <code>SpicaOracleResultSet</code> object
 * is interleaved with the reading of another, each must have been generated
 * by different <code>SpicaOracleStatement</code> objects
 *
 * All execution methods in the <code>SpicaOracleStatement</code> interface
 * implicitly close a statment's current <code>SpicaOracleResultSet</code>
 * object if an open one exists
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      November 19, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Statement.php 1678 2010-01-16 11:03:11Z pcdinh $
 */

/**
 * Static statement interface
 */
include_once 'library/spica/core/datasource/db/Statement.php';

/**
 * Result set interface
 */
include_once 'library/spica/core/datasource/db/ResultSet.php';

class SpicaOracleStatement extends SpicaOracleCommonStatement implements SpicaStatement
{
    /**
     * An array of queries in a batch
     *
     * @var array
     */
    protected $_batchQueries = array();

    /**
     * An array of results after batch execution
     *
     * @var array
     */
    protected $_batchResults = array();

    /**
     * The current offset of the query being executed
     *
     * @var int
     */
    protected $_currentBatchResultOffset = -1;

    /**
     * Current result set
     *
     * @var SpicaOracleResultSet
     */
    protected $_currentResultSet;

    /**
     * The number of affected rows as an integer
     *
     * @var int
     */
    protected $_affectedRowCount = 0;

    /**
     * Number of rows to be prefetched
     *
     * @var int
     */
    protected $_prefetchedRowCount = null;

    /**
     * Constructs an object of <code>SpicaOracleStatement</code>
     *
     * @param $dbConn SpicaOracleConnection
     */
    public function __construct($dbConn)
    {
        $this->_dbConn = $dbConn;
    }

    /**
     * Cancels this statement object if both the DBMS and driver support aborting an SQL statement
     *
     * @throws SpicaDatabaseException
     */
    public function cancel()
    {
        if (false === $this->_isClosed && null !== $this->_dbConn)
        {
            try
            {
                $conn = $this->getNativeConnection();
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to cancel the statement
                because database connection has been closed. ', null, null, null, $ex);
            }

            // FIXME
            // Logic code here

            if (false === $result)
            {
                throw new SpicaDatabaseException('Unable to cancel the statement attached with thread ID '.$id, mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
            }
        }
    }

    /**
     * Gives the Spica database driver a hint as to the number of rows that should be fetched
     * from the database when more rows are needed for this SpicaResultSet object.
     *
     * @param int $rows
     */
    public function setFetchSize($rows)
    {
        $this->_prefetchedRowCount = $rows;
    }

    /**
     * Adds the given SQL command to the current list of commmands for this SpicaStatement object
     *
     * @var string $sql
     */
    public function addBatch($sql)
    {
        $this->_guessQueryType($sql);

        if (false === $this->_isSelect)
        {
            $this->_checkUpdatable();
        }

        $this->_batchQueries[] = $sql;
    }

    /**
     * Prepares a SQL statement for execution
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $sql
     * @return SpicaOraclePreparedStatement
     */
    public function prepare($sql)
    {
        $this->_guessQueryType($sql);

        if (false === $this->_isSelect)
        {
            $this->_checkUpdatable();
        }

        try
        {
            $conn = $this->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to create a prepared statement because database connection has been closed. ', null, null, null, $ex);
        }

        require_once 'library/spica/core/datasource/db/oracle/PreparedStatement.php';
        return new SpicaOraclePreparedStatement($conn, $sql);
    }

    /**
     * Executes a query.
     *
     * @see    SpicaStatement#execute($sql)
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @return bool True if SQL command is executed successfully, false if otherwise
     */
    public function execute($sql)
    {
        // No trailing and/or leading whitespace please
        $this->_lastQuery = trim($sql);

        $this->_guessQueryType();

        if (true === $this->_isSelect)
        {
            return $this->_doRealQueryExecution($this->_lastQuery);
        }
        else
        {
            $this->_checkUpdatable();
            return $this->_doRealQueryExecution($sql);
        }
    }

    /**
     * Submits a batch of commands to the database for execution and if all commands
     * execute successfully, returns an array of update counts. The int elements
     * of the array that is returned are ordered to correspond to the commands
     * in the batch, which are ordered according to the order in which they were
     * added to the batch. The elements in the array returned by the method
     * executeBatch() may be one of the following:
     *    1. A number greater than or equal to zero -- indicates that the command
     *    was processed successfully and is an update count giving the number of
     *    rows in the database that were affected by the command's execution
     *
     *    2. A value of SUCCESS_NO_INFO -- indicates that the command was processed
     *    successfully but that the number of rows affected is unknown
     *    If one of the commands in a batch update fails to execute properly,
     *    this method throws a SpicaBatchUpdateException, and a JDBC driver may or may not
     *    continue to process the remaining commands in the batch.
     *    However, the driver's behavior must be consistent with a particular DBMS,
     *    either always continuing to process commands or never continuing to
     *    process commands. If the driver continues processing after a failure,
     *    the array returned by the method BatchUpdateException.getUpdateCounts
     *    will contain as many elements as there are commands in the batch,
     *    and at least one of the elements will be the following:
     *
     *    3. A value of EXECUTE_FAILED -- indicates that the command failed to
     *    execute successfully and occurs only if a driver continues to process
     *    commands after a command fails
     *
     * A driver is not required to implement this method. The possible implementations
     * and return values have been modified in the Java 2 SDK, Standard Edition, version 1.3
     * to accommodate the option of continuing to proccess commands in a batch update
     * after a SpicaBatchUpdateException object has been thrown.
     *
     * @throws SpicaDatabaseException when database connection is closed
     * @see    SpicaStatement#addBatch()
     * @see    SpicaStatement#executeBatch()
     * @return array An array of batch results
     */
    public function executeBatch()
    {
        // Check closed statement
        $this->_checkClosed();

        // Returns immediately if the batch is empty
        if (0 === ($totalCommand = count($this->_batchQueries)))
        {
            return array();
        }

        try
        {
            $conn = $this->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL batch command
            because database connection has been closed. ', null, null, null, $ex);
        }

        if (true === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        $rs       = array();
        $caseMode = $this->_dbConn->getSQLIdentifierCase();
        for ($i   = 0; $i < $totalCommand; $this->_batchQueries)
        {
            $this->_lastQuery = trim($this->_batchQueries[$i]);
            $stmt = $this->_createStatement($sql, $conn);
            $this->_executeStatement($stmt, $commitMode);

            if (false   === $this->_isSelect)
            {
                // We were simply executing a query that returns no result set (INSERT, DELETE, UPDATE, TRUNCATE)
                $rs[$i] = array(
                // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
                // An integer greater than zero indicates the number of rows affected or retrieved.
                // 0 indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed.
                // -1 indicates that the query returned an error
                  'affected_rows'  => oci_num_rows($stmt),
                // The value of the AUTO_INCREMENT field that was updated by the previous query.
                // Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value
                  'last_insert_id' => mysqli_insert_id($conn), // 0 (no ID) or bigger (last insert ID)
                // No result set
                  'no_resultset'   => true
                );
            }
            else
            {
                $rs[$i] = new OracleResultSet($stmt, true, $caseMode);
            }
        }

        return $rs;
    }

    /**
     * Executes the given SQL statement, which returns a single SpicaOracleResultSet object
     * like SELECT, SHOW, DESCRIBE or EXPLAIN
     *
     * @see    SpicaStatement#executeQuery
     * @param  string $sql an SQL statement to be sent to the database, typically a static SQL <code>SELECT</code> statement
     * @return SpicaOracleResultSet
     */
    public function executeQuery($sql)
    {
        $this->_guessQueryType();

        if (false === $this->_isSelect)
        {
            throw new SpicaDatabaseException('Wrong use of Statement#executeQuery().
            This method is used for CREATE, SELECT, SHOW, DESCRIBE or EXPLAIN only. ');
        }

        $this->_doRealQueryExecution($sql);
        return $this->getResultSet();
    }

    /**
     * Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement
     * or an SQL statement that returns nothing, such as an SQL DDL statement
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @see    SpicaStatement#executeUpdate($sql)
     * @param  string $sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>,
     *                    <code>UPDATE</code> or <code>DELETE</code>; or an SQL statement that
     *                    returns nothing, such as a DDL statement
     * @return bool
     */
    public function executeUpdate($sql)
    {
        if (true === $this->_dbConn->isReadOnly())
        {
            throw new SpicaDatabaseException('Connection is read-only.
            Queries leading to data modification are not allowed. ');
        }

        $this->_isSelect = false;
        return $this->_doRealQueryExecution($sql);
    }

    /**
     * Executes a SQL against database server
     *
     * @param  string $sql
     * @return bool
     */
    protected function _doRealQueryExecution($sql)
    {
        if (false === is_string($sql))
        {
            throw new SpicaDatabaseException('Invalid SQL command. ');
        }

        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection(); // establish connection
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL command
            because database connection has been closed. ', null, null, null, $ex);
        }

        $stmt    = $this->_createStatement($sql, $conn);

        if (true === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        $this->_executeStatement($stmt, $commitMode);
        $this->_affectedRowCount = oci_num_rows($stmt);
        $this->_currentResultSet = new SpicaOracleResultSet($stmt, $this->_isSelect, $this->_dbConn->getSQLIdentifierCase());
        return true;
    }

    /**
     * Gets all queries in the batch
     *
     * @return array
     */
    public function getBatchQueries()
    {
        return $this->_batchQueries;
    }

    /**
     * Moves to this SpicaStatement object's next result, deals with any current OracleResultSet object(s)
     * according to the instructions specified by the given flag, and returns true if the next result
     * is a SpicaOracleResultSet object
     *
     * There are no more results when the following is true:
     * @example
     *   // $stmt is a SpicaStatement object
     *   (($stmt->getMoreResults() == false) && ($stmt->getUpdateCount() == false))
     *
     *    while ($stmt->getMoreResults()) {
     *       $rs = $stmt->getResultSet();
     *    }
     *
     * @param  int $current
     * @return bool
     */
    public function getMoreResults($current = SpicaStatement::CLOSE_CURRENT_RESULT)
    {
        switch ($current)
        {
            case SpicaStatement::CLOSE_CURRENT_RESULT:

                if (true === is_object($this->_currentResultSet))
                {
                    $this->_currentResultSet->close();
                }

                break;

            case SpicaStatement::CLOSE_ALL_RESULTS:

                if (true === is_object($this->_currentResultSet))
                {
                    $this->_currentResultSet->close();
                }

                $this->_closeAllOpenResults();

                break;

            case SpicaStatement::KEEP_CURRENT_RESULT:
                break;
        }

        $this->_currentResultSet = $this->getNextResultSet();
        return ($this->_currentResultSet instanceof SpicaOracleResultSet) ? true: false;
    }

    /**
     * Gets next result set in the batch
     *
     * @return SpicaOracleResultSet
     */
    public function getNextResultSet()
    {
        $offset = ++$this->_previousBatchResultOffset;
        return isset($this->_batchResults[$offset]) ? $this->_batchResults[$offset] : null;
    }

    /**
     * Retrieves the current result as a SpicaOracleResultSet object
     *
     * @return SpicaOracleResultSet
     */
    public function getResultSet()
    {
        return $this->_currentResultSet;
    }

    /**
     * Gets the affected rows count after update query is excuted
     *
     * @return int|false
     */
    public function getUpdateCount()
    {
        if (false === $this->_isSelect && null !== $this->_currentResultSet &&
        true === $this->_currentResultSet->isAvailable())
        {
            try
            {
                $conn = $this->_dbConn->getNativeConnection(); // establish connection
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to retrieve number of rows that were affected by
                preceding SQL statement because database connection has been closed. ', null, null, null, $ex);
            }

            // @see SpicaOracleStatement::_doRealQueryExecution()
            return $this->_affectedRowCount;
        }

        return false;
    }

    /**
     * Returns the last insert ID.
     *
     * This method returns the current number of a certain sequence.
     *
     * @throws SpicaDatabaseException
     *
     * @param  $sequenceName The name of the sequence
     * @param  $schema       The user schema that the sequence belongs to
     * @return int|null Value of the seq column for the last INSERT
     */
    public function lastInsertId($sequenceName, $schema)
    {
        // Check closed statement
        $this->_checkClosed();

        if (!empty($schema))
        {
            $schema = $schema.'.';
        }

        $this->_lastQuery = 'SELECT '.$schema.$field.'.CURRVAL cur FROM DUAL';

        try
        {
            $id = $this->fetchOne($this->_lastQuery);
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to retrieve last insert ID. ', null, null, null, $ex);
        }

        return (int) $id;
    }

    /**
     * Returns the next number of a sequence.
     *
     * This method returns the next number of a certain sequence.
     *
     * @throws SpicaDatabaseException
     *
     * @param  $sequenceName The name of the sequence
     * @param  $schema       The user schema that the sequence belongs to
     * @return int|null Value of the seq column for the last INSERT
     */
    public function nextId($sequenceName, $schema)
    {
        // Check closed statement
        $this->_checkClosed();

        if (!empty($schema))
        {
            $schema = $schema.'.';
        }

        $this->_lastQuery = 'SELECT '.$schema.$field.'.NEXTVAL next_id FROM DUAL';

        try
        {
            $id = $this->fetchOne($this->_lastQuery);
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to retrieve the next sequence ID. ', null, null, null, $ex);
        }

        return (int) $id;
    }

    /**
     * Returns the connection ID (thread ID) for the connection
     *
     * Every connection has an ID that is unique among the set of currently connected clients.
     *
     * @see    SpicaStatement#getConnectionId()
     * @return int|null
     */
    public function getConnectionId()
    {
        // no-op
    }

    /**
     * Returns the number of rows updated, inserted, or deleted by the preceding statement
     *
     * @return int|false
     */
    public function getAffectedRowCount()
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to retrieve affected row numbers because database connection has been closed. ', null, null, null, $ex);
        }

        return $this->_affectedRowCount;
    }

    /**
     * Fetches the first column of the first row in a result set as a string
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return string|null Null value is returned when no row is found
     */
    public function fetchOne($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve a single value because database connection has been closed. ', null, null, null, $ex);
        }

        // Creates statement
        $stmt = $this->_createStatement($sql, $conn);
        // Execute statement
        $this->_executeStatement($stmt, OCI_DEFAULT);
        // Get the first row
        // Returns an indexed array with the field information, or FALSE if there are no more rows in the statement
        $row  = oci_fetch_row($stmt);
        // Frees all resources associated with statement or cursor
        oci_free_statement($stmt);

        if (true === isset($row[0]))
        {
            return $row[0];
        }

        return null;
    }

    /**
     * Fetches a single column from a result set as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return array An empty array is returned when there is no row found
     */
    public function fetchColumn($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve a single column result set because database connection has been closed. ', null, null, null, $ex);
        }

        // Creates statement
        $stmt = $this->_createStatement($sql, $conn);
        // Execute statement
        $this->_executeStatement($stmt, OCI_DEFAULT);

        $rs   = array();
        $numberOfRows = oci_fetch_all($stmt, $rs, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN+OCI_NUM);
        oci_free_statement($stmt);

        // No rows retrieved or error occurs
        if (0 === $numberOfRows || false === $numberOfRows)
        {
            return array();
        }

        return $rs[0];
    }

    /**
     * Fetches a single row from result set returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchRow($sql, $caseMode = null)
    {
        // Check closed statement
        $this->_checkClosed();
        $this->_lastQuery = $sql;

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve a single row result set because database connection has been closed. ', null, null, null, $ex);
        }

        // Creates statement
        $stmt = $this->_createStatement($sql, $conn);
        // Execute statement
        $this->_executeStatement($stmt, OCI_DEFAULT);

        $rs   = array();
        $numberOfRows = oci_fetch_all($stmt, $rs, 0, 1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);
        oci_free_statement($stmt);

        // No rows retrieved or error occurs
        if (0 === $numberOfRows || false === $numberOfRows)
        {
            return array();
        }

        // If user does not specify that field case mode
        if (null === $caseMode)
        {
            $caseMode = $this->_dbConn->getSQLIdentifierCase();
        }

        switch ($caseMode)
        {
            case SpicaResultSet::IDENTIFIER_LOWERCASE:
                $rs = array_change_key_case($rs[0], CASE_LOWER);
                break;

            case SpicaResultSet::IDENTIFIER_UPPERCASE:
                $rs = array_change_key_case($rs[0], CASE_UPPER);
                break;

            default:
                // no-op (present here for coding standard compliant)
        }

        return $rs;
    }

    /**
     * Fetches result set of multiple rows returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchAll($sql, $caseMode = null)
    {
        // Check closed statement
        $this->_checkClosed();

        $this->_lastQuery = $sql;

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve multiple-rows result set because database connection has been closed. ', null, null, null, $ex);
        }

        // Creates statement
        $stmt = $this->_createStatement($sql, $conn);
        // Execute statement
        $this->_executeStatement($stmt, OCI_DEFAULT);

        $rs   = array();
        $numberOfRows = oci_fetch_all($stmt, $rs, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);
        oci_free_statement($stmt);

        // No rows retrieved or error occurs
        if (0 === $numberOfRows || false === $numberOfRows)
        {
            return array();
        }

        // If user does not specify that field case mode
        if (null === $caseMode)
        {
            $caseMode = $this->_dbConn->getSQLIdentifierCase();
        }

        // Intentional duplicate code for performance
        if ($caseMode === SpicaResultSet::IDENTIFIER_LOWERCASE)
        {
            for ($i = 0; $i < $numberOfRows; $i++)
            {
                $rs[$i] = array_change_key_case($rs[$i], CASE_LOWER);
            }
        }
        elseif ($caseMode === SpicaResultSet::IDENTIFIER_UPPERCASE)
        {
            for ($i = 0; $i < $numberOfRows; $i++)
            {
                $rs[$i] = array_change_key_case($rs[$i], CASE_UPPER);
            }
        }

        return $rs;
    }

    /**
     * Retrieves the certain number of rows counted on a given row sequence number.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql  The SQL command
     * @param  int    $from The beginning row sequence number that will be retrieved, starting with 0
     * @param  int    $to   The last row sequence number that will be retrieved
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchRange($sql, $from = 0, $to = 20, $caseMode = null)
    {
        if ($from > $to)
        {
            throw new SpicaDatabaseException('The higher sequence row number ($from:'.htmlentities($from).') can not greater than
            the lower sequence row number ($to:'.htmlentities($to).').');
        }

        // Row at index $to is included
        $query = 'SELECT * FROM (
                     SELECT sub.*, rownum AS row_num
                     FROM ('. $sql .') sub
                  )
                  WHERE row_num BETWEEN '. ($from + 1) .' AND '. $to;

        return $this->fetchAll($query, $caseMode);
    }

    /**
     * Retrieves certain set of rows for page navigation.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql           The SQL command
     * @param  int    $pageNumber    The page sequence number
     * @param  int    $recordPerPage The number of records that will be displayed on each page
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchPage($sql, $pageNumber = 1, $recordPerPage = 20, $caseMode = null)
    {
        // Calculate the ceiling limit and floor limit
        // Ceiling limit (offset): Starting index of records to fetch from
        $offset = ($pageNumber - 1)*$recordPerPage;
        $query  = SpicaOracleCommand::modifyLimitQuery($sql, $recordPerPage, $offset);
        return $this->fetchAll($query, $caseMode);
    }

    /**
     * Fetches random row(s) from a table
     *
     * @throws SpicaDatabaseException
     * @param  string $table
     * @param  string $columns
     * @param  string $rowCount
     * @return array
     */
    public function fetchRandom($table, $columns, $rowCount = 1, $caseMode = null)
    {
        $columns = implode(', ', $columns);
        $query   = 'SELECT '.$columns.' FROM
                      (SELECT '.$columns.' FROM '.$table.'
                       ORDER BY dbms_random.value)
                    WHERE rownum <= '.$rowCount;

        return $this->fetchAll($query, $caseMode);
    }

    /**
     * Builds up a HTML table to specify what is the optimizing strategy
     * used in executing a given SQL command
     *
     * @example In plain SQL
     *
     * EXPLAIN PLAN FOR SELECT * FROM test2 WHERE username = 'pcdinh2';
     * SELECT * FROM TABLE(dbms_xplan.display);
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $sql
     * @return string A HTML table
     */
    public function explain($sql)
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve multiple-rows result set because database connection has been closed. ', null, null, null, $ex);
        }

        $analysisText     = false;
        $stmtId           = substr(md5($sql), 0, 30);
        $this->_lastQuery = 'EXPLAIN PLAN SET STATEMENT_ID = \'' . $stmtId . '\' FOR ' . $sql;
        $stmt             = oci_parse($conn, $this->_lastQuery);
        $success          = oci_execute($stmt);

        if ($success)
        {
            // note: we might make the name of the explain plan table an ini variable...
            // note 2: since oracle 9, a package is provided that we could use to get nicely formatted explain plan output: DBMS_XPLAN.DISPLAY
            //         but we should check if it is installe or not
            //         "SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', '$stmtId'))";
            oci_free_statement($stmt);
            $stmt = oci_parse($conn, 'SELECT LPAD(\' \', 2*(LEVEL - 1))||operation operation,
                                             options, object_name,
                                             position, cost,
                                             cardinality, bytes,
                                             object_owner, optimizer,
                                             cpu_cost, io_cost
                                      FROM plan_table
                                      START WITH id = 0 AND statement_id = \''.$stmtId.'\'
                                      CONNECT BY PRIOR id = parent_id AND statement_id = \''.$stmtId.'\'');
            $success = oci_execute($stmt);

            if ($success)
            {
                $rows    = array();
                $numRows = oci_fetch_all($stmt, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);

                // Figure out all columns and their maximum display size
                $columns = array();

                foreach ($rows as $row)
                {
                    foreach ($row as $col => $data)
                    {
                        if (!isset($columns[$col]))
                        {
                            $columns[$col] = array('name' => $col, 'size' => strlen($col));
                        }

                        $columns[$col]['size'] = max($columns[$col]['size'], strlen($data));
                    }
                }

                $analysisText  = '';
                $delimiterLine = array();

                // Generate the column line and the vertical delimiter
                // The look of the table is taken from the MySQL CLI client
                // It looks like this:
                // +-------+-------+
                // | col_a | col_b |
                // +-------+-------+
                // | txt   |    42 |
                // +-------+-------+
                foreach ($columns as $col)
                {
                    $delimiterLine[] = str_repeat('-', $col['size'] + 2);
                    $colLine[]       = ' '.str_pad( $col['name'], $col['size'], ' ', STR_PAD_RIGHT).' ';
                }

                $delimiterLine = '+' . join('+', $delimiterLine) . "+\n";
                $analysisText  = $delimiterLine;
                $analysisText .= '|' . join('|', $colLine) . "|\n";
                $analysisText .= $delimiterLine;

                // Go through all data and pad them to create the table correctly
                foreach ($rows as $row)
                {
                    $rowLine = array();

                    foreach ($columns as $col)
                    {
                        $name      = $col['name'];
                        $size      = $col['size'];
                        $data      = isset( $row[$name] ) ? $row[$name] : '';
                        // Align numerical values to the right (ie. pad left)
                        $rowLine[] = ' '.str_pad( $row[$name], $size, ' ', is_numeric($row[$name]) ? STR_PAD_LEFT:STR_PAD_RIGHT) . ' ';
                    }

                    $analysisText .= '|' . join( '|', $rowLine ) . "|\n";
                    $analysisText .= $delimiterLine;
                }

                // Reduce memory usage
                unset( $rows, $delimiterLine, $colLine, $columns );
            }
        }

        oci_free_statement($stmt);

        return $analysisText;
    }

    /**
     * Insert an array of data into the table where there are some LOB field
     *
     * @param  string $table
     * @param  string $data
     * @return bool
     * @throws Exception when something is wrong
     */
    public function insertClob($table, $data = array())
    {
        $this->connect();

        if (true === empty($data))
        {
            return false;
        }

        $columns = array_keys($data);

        if (true === empty($columns))
        {
            return false;
        }

        // Prepare to binding column placeholder
        $columnPlaceholder       = '';
        $columnListings          = '';

        $clobColumns             = array();

        $returnColumnPlaceholder = '';
        $returnColumnListings    = '';

        foreach ($data as $columnName => $spec)
        {
            // insert into clause
            if ('' === $columnListings)
            {
                $columnListings .= $columnName;
            }
            else
            {
                $columnListings .= ', '.$columnName;
            }

            if (true === is_array($spec))
            {
                if (true === isset($spec['value']))
                {
                    $clobColumns[$columnName]     = $spec['value'];

                    // Return clause
                    if ('' === $returnColumnPlaceholder)
                    {
                        $returnColumnPlaceholder .= ':'.$columnName;
                        $returnColumnListings    .= $columnName;
                    }
                    else
                    {
                        $returnColumnPlaceholder .= ', :'.$columnName;
                        $returnColumnListings    .= ', '.$columnName;
                    }

                    // Values clause
                    if ('' === $columnPlaceholder)
                    {
                        $columnPlaceholder .= 'EMPTY_CLOB()';
                    }
                    else
                    {
                        $columnPlaceholder .= ', EMPTY_CLOB()';
                    }
                }
            }
            else
            {
                // Values clause
                if ('' === $columnPlaceholder)
                {
                    $columnPlaceholder .= ':'.$columnName;
                }
                else
                {
                    $columnPlaceholder .= ', :'.$columnName;
                }
            }
        }

        $sql = 'INSERT INTO '.$table.' ('.$columnListings.')
                VALUES ('.$columnPlaceholder.')
                RETURNING '.$returnColumnListings.' INTO '.$returnColumnPlaceholder;

        // If the statement does not exist, create it.
        try
        {
            // If the statement does not exist, create it.
            $this->createStatement($sql, false);
        }
        catch (Exception $ex)
        {
            throw new Exception('Unable to execute the update query due to statement creation failure: '.$ex->getMessage());
        }

        $binded = true;

        foreach ($data as $columnName => $spec)
        {
            if (false   === is_array($spec))
            {
                $binded = oci_bind_by_name($this->_statement, ':'.$columnName, $data[$columnName], -1);
            }
            else
            {
                $clob[$columnName] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                $binded = oci_bind_by_name($this->_statement, $columnName, $clob[$columnName], -1, OCI_B_CLOB);
            }

            if (false === $binded)
            {
                $info = oci_error($this->_statement);
                throw new Exception('Can not bind the placeholder. '.__METHOD__, $info['code'], $info);
            }
        }

        try
        {
            // Transactionally
            $this->execute($this->_statement, OCI_DEFAULT);
        }
        catch (Exception $ex)
        {
            throw new Exception('Unable to execute the update statement: '.$ex->getMessage());
        }

        foreach ($clobColumns as $columnName => $value)
        {
            $isSaveSuccess = $clob[$columnName]->save($value);

            if (false === $isSaveSuccess)
            {
                $info = oci_error($this->_statement);
                throw new Exception('Can not saves large object data to column named '.$columnName.' using method '.__METHOD__, $info['code'], $info);
            }
        }

        if (true === $this->_autoCommit)
        {
            $this->commit();

            foreach ($clobColumns as $columnName => $value)
            {
                // Free resources
                oci_free_statement($this->_statement);
                $clob[$columnName]->free();
                unset($clob[$columnName]);
            }
        }
        else
        {
            // Make it exposed to resource freeing
            $this->_clob = $clob;
        }

        return true;
    }

    /**
     * Inserts a single or multiple records into a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array $data An array of user input with specification: [field => value]
     * @return int An integer greater than zero indicates the number of rows affected or retrieved
     */
    public function insert($tableName, $data)
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        if (false === is_array($data) || 0 === count($data))
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command because the second parameter to insert() method is not a non-empty array. ', null, null, null);
        }

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s INSERT command
            because database connection has been closed. ', null, null, null);
        }

        foreach ($data as $field => $value)
        {
            $fields[] = $field ;

            if (null === $value)
            {
                $values[] = 'NULL';
                continue;
            }

            $values[] = '\'' . $this->_dbConn->quote($value) . '\'';
        }

        $this->_lastQuery = 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';

        // Execute this query with OracleStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $stmt = oci_parse($conn, $this->_lastQuery);

        // Make duplicate code here for inline optimization
        if (false === $stmt)
        {
            $err = oci_error($conn);
            throw new SpicaDatabaseException('Unable to parse INSERT INTO command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        if (false === oci_execute($stmt, $commitMode))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute INSERT INTO command. ', $err['message'], $err['code'], $err['code']);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        $affectedRowCount = oci_num_rows($stmt);
        oci_free_statement($stmt);
        return $affectedRowCount;
    }

    /**
     * Updates data in a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string       $tableName
     * @param  array        $data An array of user input with specification: [field => value]
     * @param  string|array $where Array of fields and its conditions (array('id' => '> 3')
     *                      or conditional phrase for UPDATE SQL command
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function update($tableName, $data, $where = array())
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s UPDATE command
            because database connection has been closed. ', null, null, null);
        }

        foreach ($data as $field => $value)
        {
            if (null    === $value)
            {
                $pair[] = $field . ' = NULL';
                continue;
            }

            $pair[]     = $field . ' = \'' . $this->_dbConn->quote($value) . '\'';
        }

        // Conditional phrase
        $where = SpicaOracleCommand::buildWherePhrase($where, $this->_dbConn);

        if (false === $where)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s UPDATE command because WHERE phrase is not valid', null, null, null);
        }

        $this->_lastQuery = 'UPDATE ' . $tableName . ' SET ' . implode(', ', $pair) . $where;

        // Execute this query with OracleStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $stmt = oci_parse($conn, $this->_lastQuery);

        // Make duplicate code here for inline optimization
        if (false === $stmt)
        {
            $err = oci_error($conn);
            throw new SpicaDatabaseException('Unable to parse UPDATE command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        if (false === oci_execute($stmt, $commitMode))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute UPDATE command. ', $err['message'], $err['code'], $err['code']);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        $affectedRowCount = oci_num_rows($stmt);
        oci_free_statement($stmt);
        return $affectedRowCount;
    }

    /**
     * Deletes a single row or multiple rows in a table, using an array as user input conditions
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array|string $where Conditional phrase for DELETE SQL command.
     *                      As an arrray: [field => condition]. E.x : array('id' => null, 'name' = 'LIKE %pcdinh%')
     *                      As a string: "id = 3 AND username = 'pcdinh'" (without WHERE)
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an DELETE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function delete($tableName, $where = array())
    {
        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s DELETE command
            because database connection has been closed. ', null, null, null);
        }

        // Conditional phrase
        $where = SpicaOracleCommand::buildWherePhrase($where, $this->_dbConn);

        if (false === $where)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s DELETE command because WHERE conditional phrase is not valid. ', null, null, null);
        }

        $this->_lastQuery = 'DELETE FROM ' . $tableName . $where;

        // Execute this query with OracleStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $stmt = oci_parse($conn, $this->_lastQuery);

        // Make duplicate code here for inline optimization
        if (false === $stmt)
        {
            $err  = oci_error($conn);
            throw new SpicaDatabaseException('Unable to parse DELETE command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true  === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        if (false === oci_execute($stmt, $commitMode))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute DELETE command. ', $err['message'], $err['code'], $err['code']);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        $affectedRowCount = oci_num_rows($stmt);
        oci_free_statement($stmt);
        return $affectedRowCount;
    }

    /**
     * Alters an existing entire record or multiple records or create new one(s)
     * based on an unique index (with single or multiple keys) or a primary key,
     * using an array as user input
     *
     * Using MERGE to insert a record will keep you from inserting duplicate records into your table,
     * providing they have a unique index or a primary key
     *
     * Use the MERGE statement to select rows from one table for update or insertion into another table.
     * The decision whether to update or insert into the target table is based on a condition in the ON
     * clause.
     *
     * This statement is a convenient way to combine at least two operations.
     * It lets you avoid multiple INSERT and UPDATE DML statements.
     *
     * Only for Oracle 9i or greater
     *
     * @example
     *
     * MERGE INTO schema_name.table_name B
     * USING (
     *    SELECT '028-01' column_1, '25' column_2, '6' column_3, '46603404838' column_4
     *    FROM DUAL) E
     * ON B.column_4 = E.column_4 -- Unique index comparison
     * WHEN MATCHED THEN
     *    UPDATE SET B.column_1 = E.column_1, B.column_2 = E.column_2, B.column_3 = E.column_3
     * WHEN NOT MATCHED THEN
     *    INSERT (B.column_1, B.column_2, B.column_3, B.column_4)
     *    VALUES (B.column_1, B.column_2, B.column_3, B.column_4);
     *
     * @see    http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
     * @see    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#i2081218
     * @see    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  array  $options Optional arguments. Index key 'ref_cols' must not contain
     *                field names that are available in second argument $data
     *                or you will get ORA-38104: Columns referenced in the ON Clause cannot be updated
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function replace($tableName, $data, $options = array())
    {
        if (false === isset($options['ref_cols']) || 0 === count($options['ref_cols']))
        {
            throw new SpicaDatabaseException('Method replace() in Oracle driver requires the third argument with ref_cols as an index to a non-empty array.');
        }

        // throws SpicaDatabaseException
        $this->_checkUpdatable();
        $values  = array();
        $colVal  = array();
        $update  = array();
        $count   = 0;

        foreach ($data as $field => $value)
        {
            $fields[]     = 't.'.$field;

            if (null      === $value)
            {
                $values[] = 'NULL';
                $colVal[] = 'NULL '.$field;
            }
            else
            {
                $value    = $this->_dbConn->quote($value);
                $values[] = '\'' . $value . '\'';
                $colVal[] = '\'' . $value . '\' '.$field;
            }

            // Don't update a column that is needed as part of the join condition
            // or you will get ORA-38104: Columns referenced in the ON Clause cannot be updated
            if (false === in_array($field, $options['ref_cols']))
            {
                $update[] = 't.'.$field.' = v.'.$field;
            }

            $count++;
        }

        $indexCond = array();

        foreach ($options['ref_cols'] as $column)
        {
            $indexCond[]  = 't.'.$column.' = v.'.$column;
        }

        $indexCond = implode(' AND ', $indexCond);

        // The expresion ON (....) is required.
        // If the parentheses are left off, then an "ORA-00969: missing ON keyword" error results.
        // Not sure why
        $this->_lastQuery = 'MERGE INTO '.$tableName.' t
                             USING (
                               SELECT ' . implode(', ', $colVal) . '
                               FROM dual) v
                             ON ('.$indexCond.')
                             WHEN MATCHED THEN
                               UPDATE SET ' . implode(', ', $update) . '
                             WHEN NOT MATCHED THEN
                               INSERT (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s MERGE command because database connection has been closed. ', null, null, null);
        }

        // Execute this query with OracleStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $stmt     = oci_parse($conn, $this->_lastQuery);

        // Make duplicate code here for inline optimization
        if (false === $stmt)
        {
            $err  = oci_error($conn);
            throw new SpicaDatabaseException('Unable to parse MERGE command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true  === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        if (false === oci_execute($stmt, $commitMode))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute MERGE command. ', $err['message'], $err['code'], $err['code']);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        $affectedRowCount = oci_num_rows($stmt);
        oci_free_statement($stmt);
        return $affectedRowCount;
    }

    /**
     * Inserts an entire existing record based on an unique index (with single or multiple keys)
     * or a primary key by new one(s) or update the current matching one(s).
     *
     * Using MERGE to insert a record will keep you from inserting duplicate records into your table,
     * providing they have a unique index or a primary key
     *
     * Use the MERGE statement to select rows from one table for update or insertion into another table.
     * The decision whether to update or insert into the target table is based on a condition in the ON
     * clause.
     *
     * This statement is a convenient way to combine at least two operations.
     * It lets you avoid multiple INSERT and UPDATE DML statements.
     *
     * @example
     *
     * -- Both clauses present.
     * MERGE INTO test1 a
     *   USING all_objects b
     *       ON (a.object_id = b.object_id)
     *   WHEN MATCHED THEN
     *      UPDATE SET a.status = b.status
     *   WHEN NOT MATCHED THEN
     *      INSERT (object_id, status)
     *      VALUES (b.object_id, b.status);
     *
     * -- No matched clause, insert only.
     * MERGE INTO test1 a
     *   USING all_objects b
     *       ON (a.object_id = b.object_id)
     *   WHEN NOT MATCHED THEN
     *   INSERT (object_id, status)
     *   VALUES (b.object_id, b.status);
     *
     * -- No not-matched clause, update only.
     * MERGE INTO test1 a
     *   USING all_objects b
     *       ON (a.object_id = b.object_id)
     *   WHEN MATCHED THEN
     *       UPDATE SET a.status = b.status;
     *
     * -- An optional DELETE WHERE clause can be used to clean up after a merge operation.
     * -- Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.
     * MERGE INTO test1 a
     *   USING all_objects b
     *       ON (a.object_id = b.object_id)
     *   WHEN MATCHED THEN
     *       UPDATE SET a.status = b.status
     *       WHERE  b.status != 'VALID'
     *       DELETE WHERE (b.status = 'VALID')
     * FIXME
     * @param  $tableName
     * @param  $data
     * @return int
     */
    public function merge($tableName, $data, $options = array())
    {
        if (false === isset($options['ref_cols']) || 0 === count($options['ref_cols']))
        {
            throw new SpicaDatabaseException('Method replace() in Oracle driver requires the third argument with ref_cols as an index to a non-empty array.');
        }

        // throws SpicaDatabaseException
        $this->_checkUpdatable();

        $values  = array();
        $colVal  = array();
        $update  = array();
        $count   = 0;
        foreach ($data as $field => $value)
        {
            $fields[]     = 't.'.$field;

            if (null      === $value)
            {
                $values[] = 'NULL';
                $colVal[] = 'NULL '.$field;
            }
            else
            {
                $value    = $this->_dbConn->quote($value);
                $values[] = '\'' . $value . '\'';
                $colVal[] = '\'' . $value . '\' '.$field;
            }

            // Don't update a column that is needed as part of the join condition
            // or you will get ORA-38104: Columns referenced in the ON Clause cannot be updated
            if (false === in_array($field, $options['ref_cols']))
            {
                $update[] = 't.'.$field.' = v.'.$field;
            }

            $count++;
        }

        $indexCond = array();

        foreach ($options['ref_cols'] as $column)
        {
            $indexCond[]  = 't.'.$column.' = v.'.$column;
        }

        $indexCond = implode(' AND ', $indexCond);

        // The expresion ON (....) is required.
        // If the parentheses are left off, then an "ORA-00969: missing ON keyword" error results.
        // Not sure why
        $this->_lastQuery = 'MERGE INTO '.$tableName.' t
                             USING (
                               SELECT ' . implode(', ', $colVal) . '
                               FROM dual) v
                             ON ('.$indexCond.')
                             WHEN MATCHED THEN
                               UPDATE SET ' . implode(', ', $update) . '
                             WHEN NOT MATCHED THEN
                               INSERT (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL\'s MERGE command
            because database connection has been closed. ', null, null, null);
        }

        // Execute this query with OracleStatement::execute() method is not neccessary
        // We don't need to make things complicated here
        $stmt     = oci_parse($conn, $this->_lastQuery);

        // Make duplicate code here for inline optimization
        if (false === $stmt)
        {
            $err  = oci_error($conn);
            throw new SpicaDatabaseException('Unable to parse MERGE command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true  === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        if (false === oci_execute($stmt, $commitMode))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute MERGE command. ', $err['message'], $err['code'], $err['code']);
        }

        // Returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query
        $affectedRowCount = oci_num_rows($stmt);
        oci_free_statement($stmt);
        return $affectedRowCount;
    }

    /**
     * Drops an index.
     *
     * @throws SpicaDatabaseException
     * @param string $table The table of the index.
     * @param string $name The full index name.
     */
    public function dropIndex($table, $name)
    {
        $this->executeQuery('DROP INDEX '.$name);
    }

    /**
     * Creates a sequence, optionally starting at a certain number.
     *
     * @throws SpicaDatabaseException
     * @param string $name  The sequence name to create.
     * @param int    $start The first sequence number to return.
     * @param int    $incrementStep The increment step
     */
    public function createSequence($name, $start = 1, $incrementStep = 1)
    {
        $this->executeQuery('CREATE SEQUENCE '.$name.' START WITH '.$start.' INCREMENT BY '.$incrementStep.' NOCACHE');
    }

    /**
     * Drops a sequence.
     *
     * @throws SpicaDatabaseException
     * @param string $name The sequence name to drop.
     */
    public function dropSequence($name)
    {
        $this->executeQuery('DROP SEQUENCE '.$name);
    }

    /**
     * Fetches the records from a cursor
     *
     * A cursor is a mechanism by which you can assign a name to a "select statement"
     * and manipulate the information within that SQL statement.
     *
     * FIXME
     * @param  string $sql
     * @param  string $cursorPlaceholder
     * @return array
     */
    public function fetchCursor($sql, $cursorPlaceholder)
    {
        $data = array();
        $conn = $this->getNativeConnection();

        $curs = oci_new_cursor($conn);
        $stmt = oci_parse($conn, $sql);

        foreach ($bind as $key => &$val)
        {
            oci_bind_by_name($stmt, $key, $val, -1);
        }

        if (false === oci_execute($stmt))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true  === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        oci_execute($curs, $commitMode);

        if (oci_fetch_all($curs, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC))
        {
            // FIXME
        }

        oci_free_statement($stmt);
        oci_free_statement($curs);

        return $data;
    }

    /**
     * Gets the last query
     *
     * @return string
     */
    public function getLastQuery()
    {
        return $this->_lastQuery;
    }

    /**
     * FIXME
     */
    public function getGeneratedKeys()
    {

    }

    /**
     * Cleans results and queries stored in the current batch
     */
    public function clearBatch()
    {
        $this->_clearBatchResult();
        $this->_batchQueries = array();
    }

    /**
     * Closes the statement actually
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isClosed)
        {
            return true;
        }

        $this->_closeAllOpenResults();
        $this->_currentResultSet = null;
        $this->_isClosed         = true;

        return true;
    }

    /**
     * Frees resources stored in the current batch results
     *
     * @see SpicaOracleStatement::clearBatch()
     */
    protected function _clearBatchResult($offset = 0)
    {
        $totalResult = count($this->_batchResults);

        if ($offset >= $totalResult)
        {
            throw new InvalidArgumentException('Unable to clean batch result numbered '.$offset.'.
            Its offset exceeds the total batch result available. ');
        }

        for ($i = $offset; $i < $totalResult; $i++)
        {
            if (true === is_resource($this->_batchResults[$i]))
            {
                oci_cancel($this->_batchResults[$i]);
                oci_free_statement($this->_batchResults[$i]);
            }
        }

        $this->_batchResults = array();
    }

    /**
     * Closes all open result set in the batch
     *
     * @see SpicaOracleStatement::_realClose()
     */
    protected function _closeAllOpenResults()
    {
        if (null !== $this->_batchResults)
        {
            foreach ($this->_batchResults as $num => $rs)
            {
                /* @var $rs OracleResultSet */
                $rs->close();
            }
        }

        $this->_batchResults = null;
        $this->_currentBatchResultOffset = -1;
    }

    /**
     * Creates a SQL statement after validating SQL command
     *
     * @throws SpicaDatabaseException
     * @param  string $sql
     * @param  resource $conn OCI connection handler
     * @return resource oci8 statement
     */
    protected function _createStatement($sql, $conn)
    {
        $stmt = oci_parse($conn, $sql);

        if (false === $stmt)
        {
            $err  = oci_error($conn);
            throw new SpicaDatabaseException('Unable to parse SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        return $stmt;
    }

    /**
     * Creates a parsed statement
     *
     * @throws SpicaDatabaseException
     * @param  resource $stmt oci8 statement
     * @param  int      $commitMode Commit mode
     * @return true
     */
    protected function _executeStatement($stmt, $commitMode)
    {
        $result   = oci_execute($stmt, $commitMode);

        if (false === $result)
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Could not execute a SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true === $this->_isSelect && null !== $this->_prefetchedRowCount)
        {
            oci_set_prefetch($this->_prefetchedRowCount);
        }

        return true;
    }

    /**
     * Destroys the current <code>SpicaOracleStatement</code> object
     *
     */
    public function __destruct()
    {

    }
}

/**
 * A table of data representing a database result set, which is usually generated by
 * executing a statement that queries the database
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      November 19, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 */
class SpicaOracleResultSet extends SpicaOracleCommonResultSet implements SpicaResultSet
{
    /**
     * Constructs an object of <code>SpicaOracleResultSet</code>
     *
     * @param OCI resource|bool $stmt Oracle statement identifier or boolean value
     * @param bool $readQuery
     * @param bool $fieldCase
     */
    public function __construct($stmt, $readQuery, $fieldCase)
    {
        $this->_isAvailable = $readQuery;
        $this->_stmt        = $stmt;
        $this->_isSelect    = $readQuery;
        $this->_fieldCase   = $fieldCase;
    }

    /**
     * Destroys the current <code>SpicaOracleResultSet</code>
     */
    public function __destruct()
    {

    }
}
?>